﻿using System;
using System.Data;
using Infragistics.Win;
using System.Data.OleDb;
using System.Data.SQLite;
using System.Windows.Forms;
using Infragistics.Win.UltraWinGrid;

namespace DMSClient
{
    public partial class ValidateProductMaster : Form
    {
        private string file;
        private DataTable[] classDescriptions;

        public ValidateProductMaster()
        {
            InitializeComponent();
            classDescriptions = new DataTable[9];
            ultraGrid1.DisplayLayout.CaptionVisible = DefaultableBoolean.True;
            ultraGrid2.DisplayLayout.CaptionVisible = DefaultableBoolean.True;
            ultraGrid3.DisplayLayout.CaptionVisible = DefaultableBoolean.True;
            ultraGrid4.DisplayLayout.CaptionVisible = DefaultableBoolean.True;
            ultraGrid5.DisplayLayout.CaptionVisible = DefaultableBoolean.True;
            ultraGrid6.DisplayLayout.CaptionVisible = DefaultableBoolean.True;
            ultraGrid7.DisplayLayout.CaptionVisible = DefaultableBoolean.True;
            ultraGrid8.DisplayLayout.CaptionVisible = DefaultableBoolean.True;
            ultraGrid9.DisplayLayout.CaptionVisible = DefaultableBoolean.True;
        }

        private void ubLoad_Click(object sender, EventArgs e)
        {
            ultraGrid1.DataSource = null;
            ultraGrid2.DataSource = null;
            ultraGrid3.DataSource = null;
            ultraGrid4.DataSource = null;
            ultraGrid5.DataSource = null;
            ultraGrid6.DataSource = null;
            ultraGrid7.DataSource = null;
            ultraGrid8.DataSource = null;
            ultraGrid9.DataSource = null;

            ultraGrid1.Text = string.Empty;
            ultraGrid2.Text = string.Empty;
            ultraGrid3.Text = string.Empty;
            ultraGrid4.Text = string.Empty;
            ultraGrid5.Text = string.Empty;
            ultraGrid6.Text = string.Empty;
            ultraGrid7.Text = string.Empty;
            ultraGrid8.Text = string.Empty;
            ultraGrid9.Text = string.Empty;

            OpenFileDialog fDialog = new OpenFileDialog();
            fDialog.Filter = "Excel Files|*.xls;*.xlsx";
            fDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
            if (fDialog.ShowDialog() == DialogResult.OK)
            {
                this.file = fDialog.FileName;
                activateControls(false);
                bgwValidateFile.RunWorkerAsync();
            }
        }

        private void bgwValidateFile_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e)
        {
            string connstr = string.Empty;
            if (this.file.EndsWith("xls"))
            {
                connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.file + 
                    ";Extended Properties=Excel 8.0";
            }
            else
            {
                connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + this.file + 
                    ";Extended Properties=Excel 12.0;";
            }
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = new OleDbConnection(connstr);
            cmd.CommandText = "SELECT * FROM [ImportProduct$]";
            cmd.CommandType = CommandType.Text;
            
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet("dsProduct");
            DataTable d = new DataTable();
            try
            {
                cmd.Connection.Open();
                da.Fill(ds);
                cmd.Connection.Close();
            }
            catch (Exception ex)
            {
                cmd.Connection.Close();
                throw new Exception("Unable to open the file or it doesn't have a sheet named ImportProduct", ex.InnerException);
            }

            string[] columnsNames = { "cdProduct", "cdStatus", "dsProduct", "cdClass1", 
                                      "nmClass1", "cdClass2", "nmClass2", "cdClass3 ", 
                                      "nmClass3", "cdClass4", "nmClass4", "cdClass5", 
                                      "nmClass5", "cdClass6", "nmClass6", "cdClass7", 
                                      "nmClass7", "cdClass8", "nmClass8", "cdUnit", 
                                      "cdEAN", "cdVolumeUnit", "vlConversionUnit", "vlConversionKGLT" };
            if (ds.Tables[0].Columns.Count < columnsNames.Length)
            {
                throw new Exception("Invalid number of columns. Expected: " + columnsNames.Length + " but Found: " + ds.Tables[0].Columns.Count);
            }
            for (int i = 0; i < columnsNames.Length; i++)
            {
                if (!columnsNames[i].Equals(ds.Tables[0].Columns[i].Caption))
                {
                    throw new Exception("Unknown column name '" + ds.Tables[0].Columns[i].Caption + "' for column " + (i + 1) + ", it should be named '" + columnsNames[i] + "'");
                }
            }

            SQLiteCommand sqlcmd = new SQLiteCommand();
            sqlcmd.Connection = new SQLiteConnection("Data Source=" + Application.StartupPath + "\\DMSToolsClientDB.sqlite;Password=Nu&<0.$U><,+N.|uAi73+X~vkqj~?_fh6Wgtdq1&+Kfk|{VF]hbE&lVD6&M-7jeD");
            sqlcmd.CommandType = CommandType.Text;
            try
            {
                sqlcmd.Connection.Open();
                sqlcmd.CommandText = "CREATE TABLE IF NOT EXISTS \"product\" (\"cdsku\" VARCHAR, \"idstatus\" VARCHAR, \"dsproduct\" VARCHAR, \"nmclassiflevel1\" VARCHAR, \"nmclassification1\" VARCHAR, \"nmclassiflevel2\" VARCHAR, \"nmclassification2\" VARCHAR, \"nmclassiflevel3\" VARCHAR, \"nmclassification3\" VARCHAR, \"nmclassiflevel4\" VARCHAR, \"nmclassification4\" VARCHAR, \"nmclassiflevel5\" VARCHAR, \"nmclassification5\" VARCHAR, \"nmclassiflevel6\" VARCHAR, \"nmclassification6\" VARCHAR, \"nmclassiflevel7\" VARCHAR, \"nmclassification7\" VARCHAR, \"nmclassiflevel8\" VARCHAR, \"nmclassification8\" VARCHAR, \"cdunit\" VARCHAR, \"cdEAN\" VARCHAR, \"cdVolumeUnit\" VARCHAR, \"vlConversionUnit\" VARCHAR, \"vlConversionKGLT\" VARCHAR)";
                sqlcmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception("Error trying to write to temporal database file.", ex.InnerException);
            }
            try
            {
                sqlcmd.Transaction = sqlcmd.Connection.BeginTransaction();
                sqlcmd.CommandText = "DELETE FROM product";
                sqlcmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                sqlcmd.Transaction. Rollback();
                sqlcmd.Connection.Close();
                throw new Exception("Error trying to write to temporary database file.", ex.InnerException);
            }

            string sql = string.Empty;
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                sql = "INSERT INTO product VALUES(" +
                    "'" + cleanString(dr[columnsNames[0]].ToString()) + "'," +
                    "'" + cleanString(dr[columnsNames[1]].ToString()) + "'," +
                    "'" + cleanString(dr[columnsNames[2]].ToString()) + "'," +
                    "'" + cleanString(dr[columnsNames[3]].ToString()) + "'," +
                    "'" + cleanString(dr[columnsNames[4]].ToString()) + "'," +
                    "'" + cleanString(dr[columnsNames[5]].ToString()) + "'," +
                    "'" + cleanString(dr[columnsNames[6]].ToString()) + "'," +
                    "'" + cleanString(dr[columnsNames[7]].ToString()) + "'," +
                    "'" + cleanString(dr[columnsNames[8]].ToString()) + "'," +
                    "'" + cleanString(dr[columnsNames[9]].ToString()) + "'," +
                    "'" + cleanString(dr[columnsNames[10]].ToString()) + "'," +
                    "'" + cleanString(dr[columnsNames[11]].ToString()) + "'," +
                    "'" + cleanString(dr[columnsNames[12]].ToString()) + "'," +
                    "'" + cleanString(dr[columnsNames[13]].ToString()) + "'," +
                    "'" + cleanString(dr[columnsNames[14]].ToString()) + "'," +
                    "'" + cleanString(dr[columnsNames[15]].ToString()) + "'," +
                    "'" + cleanString(dr[columnsNames[16]].ToString()) + "'," +
                    "'" + cleanString(dr[columnsNames[17]].ToString()) + "'," +
                    "'" + cleanString(dr[columnsNames[18]].ToString()) + "'," +
                    "'" + cleanString(dr[columnsNames[19]].ToString()) + "'," +
                    "'" + cleanString(dr[columnsNames[20]].ToString()) + "'," +
                    "'" + cleanString(dr[columnsNames[21]].ToString()) + "'," +
                    "'" + cleanString(dr[columnsNames[22]].ToString()) + "'," +
                    "'" + cleanString(dr[columnsNames[23]].ToString()) + "')";
                sqlcmd.CommandText = sql;
                try
                {
                    sqlcmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    sqlcmd.Transaction.Rollback();
                    sqlcmd.Connection.Close();
                    throw new Exception("Error trying to write to temporal database file.", ex.InnerException);
                }
            }
            try
            {
                sqlcmd.Transaction.Commit();
            }
            catch (Exception ex)
            {
                sqlcmd.Connection.Close();
                throw new Exception("Error trying to write to temporal database file.", ex.InnerException);
            }
            ds.Tables.Clear();
            sqlcmd.Transaction = null;

            classDescriptions[0] = new DataTable();
            sqlcmd.CommandText = "SELECT cdsku SKU,COUNT(1) OCCURRENCES FROM product WHERE cdsku IS NOT NULL AND cdsku <> '' GROUP BY cdsku HAVING COUNT(1) > 1";
            SQLiteDataAdapter sqlda = new SQLiteDataAdapter(sqlcmd);
            sqlda.Fill(classDescriptions[0]);

            sqlcmd.CommandText = "CREATE TABLE IF NOT EXISTS \"classes\" (\"id\" VARCHAR, \"descripcion\" VARCHAR)";
            try
            {
                sqlcmd.ExecuteNonQuery();
                sqlcmd.CommandText = "DELETE FROM classes";
                sqlcmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                sqlcmd.Connection.Close();
                throw new Exception("Error trying to write to temporal database file.", ex.InnerException);
            } 
            for (int i = 1; i < 9; i++)
            {
                sqlcmd.CommandText = "INSERT INTO classes " +
                                     "SELECT nmclassiflevel" + i + ",nmclassification" + i + " " +
                                     "FROM product WHERE cdsku IS NOT NULL AND cdsku <> '' " +
                                     "GROUP BY nmclassiflevel" + i + ",nmclassification" + i + "";
                try
                {
                    sqlcmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    sqlcmd.Connection.Close();
                    throw new Exception("Error trying to write to temporal database file.", ex.InnerException);
                }
                sqlcmd.CommandText = "SELECT * FROM classes WHERE id IN (SELECT id FROM classes GROUP BY id HAVING COUNT(1) > 1)";
                classDescriptions[i] = new DataTable();
                try
                {
                    sqlda.Fill(classDescriptions[i]);
                    sqlcmd.CommandText = "DELETE FROM classes";
                    sqlcmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    sqlcmd.Connection.Close();
                    throw new Exception("Error trying to read from temporal database file.", ex.InnerException);
                } 
            }
        }

        private void bgwValidateFile_RunWorkerCompleted(object sender, System.ComponentModel.RunWorkerCompletedEventArgs e)
        {
            if (e.Error != null)
            {
                if (!this.IsDisposed)
                {
                    this.BringToFront();
                    MessageBox.Show(this, e.Error.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    activateControls(true);
                }
            }
            else if (!e.Cancelled && !this.IsDisposed)
            {
                ultraGrid1.Text = this.file;
                ultraGrid2.Text = this.file;
                ultraGrid3.Text = this.file;
                ultraGrid4.Text = this.file;
                ultraGrid5.Text = this.file;
                ultraGrid6.Text = this.file;
                ultraGrid7.Text = this.file;
                ultraGrid8.Text = this.file;
                ultraGrid9.Text = this.file;

                ultraGrid1.DataSource = classDescriptions[0];
                ultraGrid2.DataSource = classDescriptions[1];
                ultraGrid3.DataSource = classDescriptions[2];
                ultraGrid4.DataSource = classDescriptions[3];
                ultraGrid5.DataSource = classDescriptions[4];
                ultraGrid6.DataSource = classDescriptions[5];
                ultraGrid7.DataSource = classDescriptions[6];
                ultraGrid8.DataSource = classDescriptions[7];
                ultraGrid9.DataSource = classDescriptions[8];
                activateControls(true);
            }
        }

        private string cleanString(string str)
        {
            string sb = string.Empty;
            foreach (char c in str)
            {
                if ((c >= '0' && c <= '9') ||
                    (c >= 'A' && c <= 'Z') ||
                    (c >= 'a' && c <= 'z') ||
                     c == '.' || c == ',' ||
                     c == ' ' || c == '-')
                {
                    sb += c;
                }
            }
            return sb.ToString();
        }

        private void activateControls(bool value)
        {
            loader.BringToFront();
            loader.Visible = !value;
            ultraTabControl1.Enabled = value;
            ubLoad.Enabled = value;
        }

        private void ValidateProductMaster_Shown(object sender, EventArgs e)
        {
            loader.Left = (this.Width - loader.Width) / 2;
            loader.Top = ((this.Height - loader.Height) / 2) - loader.Height;
        }

        private void ValidateProductMaster_Resize(object sender, EventArgs e)
        {
            loader.Left = (this.Width - loader.Width) / 2;
            loader.Top = ((this.Height - loader.Height) / 2) - loader.Height;
        }

        private void setColumnsSize(object sender, InitializeLayoutEventArgs e)
        {
            foreach (UltraGridBand bands in e.Layout.Bands)
            {
                foreach (UltraGridColumn ugc in bands.Columns)
                {
                    ugc.PerformAutoResize(PerformAutoSizeType.AllRowsInBand, true);
                }
            }
        }
    }
}